package model.handlers;

import database.Database;
import java.sql.SQLException;
import java.util.ArrayList;
import model.*;
import utility.Converter;

/**
 * 
 * @author Sonny Sandberg
 */
public abstract class TombStoneHandler 
{
    public static int getNextCommissionID(TombStone tombStone) throws ClassNotFoundException, SQLException
    {
        Database db = new Database();
        
        String morticianID = null;
        if (tombStone.getMortician().getCustomerName().getId() > 0)
        {
            morticianID = ""+tombStone.getMortician().getCustomerName().getId();
        }
        String[][] result = db.getFromDB("bs_tombstone_order", "WHERE fk_mortician_id = "+morticianID+"", 
                                            "ORDER BY mortician_commission_no DESC", "LIMIT 1", "mortician_commission_no");
        db.closeDB();
        
        int nextID = 0;
        if (result.length > 0)
        {
            nextID = Integer.parseInt(result[0][0]);
        }
        
        if (morticianID != null)
        {
            nextID++;
        }
        else
        {
            nextID = 0;
        }        
        
        return nextID;
    }
    
    public static TombStone getLatestTombStone(TombStone tombStone) throws ClassNotFoundException, SQLException
    {
        Database db = new Database();
        
        String[][] result = db.getFromDB("bs_tombstone_order", "", 
                                            "ORDER BY id DESC", "LIMIT 1", "id");
        db.closeDB();
        if (result.length > 0)
        {
            tombStone.setId(Integer.parseInt(result[0][0]));
        }
        return tombStone;
    }
    
    /**
     * CREATE OR REPLACE VIEW getTombstoneOrder AS
        (
                SELECT
                        id AS tombID,
                        cuffin,
                        area,
                        the_row AS theRow,
                        the_number AS number,
                        old_name AS oldName,
                        room_for AS roomFor,
                        inscription,
                        font,
                        repaint,
                        commission,
                        mortician_commission_no AS mortComNum,
                        fk_mortician_id AS morticianID,
                        fk_grave_yard_id AS graveYardID	
                FROM
                        bs_tombstone_order
        );
     * @param id
     * @return
     * @throws ClassNotFoundException
     * @throws SQLException 
     */
    public static TombStone getTombStoneByID(int id) throws ClassNotFoundException, SQLException
    {
        Database db = new Database();
        
        String[][] result = db.getFromDB("getTombstoneOrder", "WHERE tombID = "+id+"", "", "", 
                                            "tombID", "cuffin", "area", "theRow", "number", "oldName", "roomFor", "inscription", 
                                            "font", "repaint", "commission", "mortComNum", "morticianID", "graveYardID");
        db.closeDB();
        
        CustomerSpecified mortican = null;
        if (result[0][12] != null)
        {
            mortican = CustomerHandler.getCustomerSpecifiedByCustomerNameID(Integer.parseInt(result[0][12]));
        }

        GraveYard graveYard = null;
        if (result[0][13] != null)
        {
            graveYard = GraveYardHandler.getGraveYardByID(Integer.parseInt(result[0][13]));
        }
        
        String[] oldName = result[0][5].split("\n");
        String[] inscrip = result[0][7].split("\n");
        
        ArrayList<String> oldNames = new ArrayList();
        for (int i = 0; i < oldName.length; i++)
        {
            oldNames.add(oldName[i]+"\n");
        }
        
        ArrayList<String> oldInscription = new ArrayList();
        for (int i = 0; i < inscrip.length; i++)
        {
            oldInscription.add(inscrip[i]+"\n");
        }
        
        TombStone tombStone = new TombStone(Integer.parseInt(result[0][0]), Integer.parseInt(result[0][3]), Integer.parseInt(result[0][4]), 0, 
                                            Integer.parseInt(result[0][6]), Double.parseDouble(result[0][10]), result[0][2], oldNames, oldInscription,                
                                            result[0][8], Converter.intToBoolean(Integer.parseInt(result[0][1])), 
                                            Converter.intToBoolean(Integer.parseInt(result[0][9])), mortican, graveYard, Integer.parseInt(result[0][11]));            
        
        return tombStone;
    }
}